package com.jdbc.crud;

import com.jdbc.handle.TestResutlHandler;
import com.jdbc.utils.JDBCUtils;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Queue;

/**
 * @author wzm
 * @create 2023-10-31 16:14
 * project_name:web2310
 */
//测试DBUtils数据操作
public class DbUtilsTest {

    //新增数据
    @Test
    public void add() throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        //conn 连接对象
        Connection conn = JDBCUtils.getConn();
        //sql
        String sql = "insert into test values(?,?)";

        int i = queryRunner.update(conn, sql, null, "王麻子");
        System.out.println(i);

        //关闭资源
        DbUtils.closeQuietly(conn);
    }

    //修改数据
    @Test
    public void update() throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        //conn 连接对象
        Connection conn = JDBCUtils.getConn();
        //sql
        String sql = "update test set name = ? where id = ?";

        int i = queryRunner.update(conn, sql,"塞班",1);
        System.out.println(i);

        //关闭资源
        DbUtils.closeQuietly(conn);
    }

    //删除数据
    @Test
    public void delete() throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        //conn 连接对象
        Connection conn = JDBCUtils.getConn();
        //sql
        String sql = "delete from test where id = ?";

        int i = queryRunner.update(conn, sql,1);
        System.out.println(i);

        //关闭资源
        DbUtils.closeQuietly(conn);
    }

    //查询全部
    @Test
    public void selectAll() throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        Connection conn = JDBCUtils.getConn();
        String sql = "select * from test";

        //将查询的结果封装为List对象
        BeanListHandler<com.jdbc.entity.Test> beanListHandler = new BeanListHandler<>(com.jdbc.entity.Test.class);

        List<com.jdbc.entity.Test> list = queryRunner.query(conn, sql, beanListHandler);
        System.out.println("list = " + list);

    }

    //查询单个数据
    @Test
    public void selectById() throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        Connection conn = JDBCUtils.getConn();
        String sql = "select * from test where id = ?";

        //封装为单个对象
        BeanHandler<com.jdbc.entity.Test> handler= new BeanHandler<>(com.jdbc.entity.Test.class);

        com.jdbc.entity.Test query = queryRunner.query(conn, sql, handler, 4);
        System.out.println("query = " + query);

    }


    //查询总数 max  min sum  count avg
    @Test
    public void selectCount() throws SQLException {
        QueryRunner queryRunner = new QueryRunner();
        Connection conn = JDBCUtils.getConn();
        String sql = "select count(*) from test";

        //将查询的结果封装单个值
        ScalarHandler handler = new ScalarHandler();

        Object query = queryRunner.query(conn, sql, handler);
        System.out.println("query = " + query);


    }


    //查询全部
    @Test
    public void selectAll01() throws SQLException {
        //执行DBUtils功能的对象
        QueryRunner queryRunner = new QueryRunner();
        Connection conn = JDBCUtils.getConn();
        String sql = "select * from test";

        //将查询的结果封装为List对象
        //自己写一个实现与ResultSetHandler的处理器
       TestResutlHandler<List<com.jdbc.entity.Test>> handler = new TestResutlHandler<>();


    /*       ResultSetHandler<List<com.jdbc.entity.Test>> handler = new ResultSetHandler<List<com.jdbc.entity.Test>>() {
            @Override
            public List<com.jdbc.entity.Test> handle(ResultSet rs) throws SQLException {
                //其实就是处理了查询到的结果集而已
                List<com.jdbc.entity.Test> list = new ArrayList<>();
                while (rs.next()){
                    list.add(new com.jdbc.entity.Test(rs.getInt(1),rs.getString(2)));
                }

                return list;
            }
        };*/


        List<com.jdbc.entity.Test> list = queryRunner.query(conn, sql, handler);
        System.out.println("list = " + list);

    }








}
